-- VIEW THE TABLE (SELECT, FROM):
SELECT *
FROM employees;

SELECT employee_name, salary, full_time
FROM employees;

-- Show employees who are full-time (WHERE):
SELECT employee_name, salary, full_time
FROM employees
WHERE full_time = 'Yes';

SELECT employee_name, salary, full_time
FROM employees
WHERE full_time = 'Yes' AND salary > 60000;

-- Sort employees by salary (ORDER BY):
SELECT employee_name, salary, full_time
FROM employees
WHERE full_time = 'Yes' AND salary > 60000
ORDER BY salary;

SELECT employee_name, salary, full_time
FROM employees
WHERE full_time = 'Yes' AND salary > 60000
ORDER BY salary DESC;

-- Show the average salary for each department (GROUP BY):
SELECT department, AVG(salary)
FROM employees
GROUP BY department
ORDER BY department;

-- Show the departments with average salary below $60,000 (HAVING):
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary < 60000
ORDER BY department;

-- Limit to 5 rows:
SELECT employee_name, salary, full_time
FROM employees
LIMIT 5;

-- Show number of full-time employees earning over $60,000 (COUNT + WHERE):
SELECT COUNT(*)
FROM employees
WHERE full_time = 'Yes' AND salary > 60000;

-- Distinct or Unique salary values only:
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC;

-- Show the sales transactions for each employee: LEFT JOIN
-- Table 1
SELECT *
FROM employees;

-- Table 2
SELECT *
FROM sales_transactions;

-- Both tables are connected by employee_id. We want the sales for each employee.
SELECT *
FROM employees LEFT JOIN sales_transactions
    ON employees.id = sales_transactions.employee_id;

-- Let's just select specific relevant columns:
SELECT employees.id, employees.employee_name,
       sales_transactions.product_name, sales_transactions.sale_amount
FROM employees LEFT JOIN sales_transactions
    ON employees.id = sales_transactions.employee_id;

